package cn.edu.nju.ws.sview.views;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import cn.edu.nju.ws.sview.cache.URIIDCache;
import cn.edu.nju.ws.sview.database.DBConnectionFactory;
import cn.edu.nju.ws.sview.database.DBOperations;
import cn.edu.nju.ws.sview.util.PropertyList;


public class Image {

	private long time = 0;
	private String groupName = null;
	private String path = null;
	private  int eType;
	private int eid;
	private int id = 0 ;
	
	
	public Image(String group, long time,String path,int etype,int eid) {
		this.groupName = group;
		this.time = time;
		this.path = path;
		this.eType = etype;
		this.eid  =eid;
	}
	
	public int getID(){
		return this.id;
	}
	
	public void setID(int id){
		this.id = id;
	}
	
	/**
	 * get the origin optimal image path of the uri. Bookmarked entity doesnot inherit the image
	 * @param uri
	 * @return empty if no statement in dereference doc is found
	 */
	public static String getOriginImage(String uri) throws Throwable{
		String imgLocation = "";
		if (uri == null) {
			return imgLocation;
		}

		ArrayList<String> properties = PropertyList.getImgProperties();
		ArrayList<String> array = new ArrayList<String>();
		for(String str:properties){
			int p = DBOperations.getURIID(str,true) ;
			array.add("u"+p) ;
		}
		
		int s = DBOperations.getURIID(uri,false);
		int doc_id = DBOperations.getDereferenceDocID(uri);
		Connection conn = DBConnectionFactory.getConnection();
		PreparedStatement stmt = null;
		stmt = conn
				.prepareStatement("SELECT o FROM quadruple WHERE s=? and doc_id=? and p=?  and o like 'u%'");
		stmt.setString(1, "u" + s);
		stmt.setInt(2, doc_id);
		for(String property:array){
			stmt.setString(3, property) ;
			ResultSet rs = stmt.executeQuery();
			if(rs.next()){
				int locationID = Integer.parseInt(rs.getString("o").substring(1));
				imgLocation = URIIDCache.getInstance().getURI(locationID);
				break;
			}
			rs.close();
		}
		stmt.close();
		conn.close();
		
		return imgLocation;		
	}	
	
	 public static ArrayList<Image> getAllImagesByEntityGroupOrderByTime(
				int eType,int eid, String groupName) {
			ArrayList<Image> images = new ArrayList<Image>();
			
			StringBuffer sqlBuffer = new StringBuffer(String.format(
					"select id, time, group_id, path " +
					"from img " +
					"where etype = %d and eid = %d ", 
					eType, eid));
			if ( groupName != null ) {
				int groupID = Group.getGroupIdByName(groupName);
				sqlBuffer.append(String.format("and group_id = %d ", groupID));
			}
			sqlBuffer.append("order by time desc ");
			
			try {
				Connection conn = DBConnectionFactory.getConnection();
				Statement stmt = conn.createStatement();
				ResultSet rs = stmt.executeQuery(sqlBuffer.toString());
				while ( rs.next() ) {
					long time = rs.getLong("time");
					int id = rs.getInt("id");
					String mgroup = Group.getGroupNameById(rs.getInt("group_id"));
	                String path = rs.getString("path");
					Image image = new Image(mgroup, time,path,eType,eid);
					image.setID(id);
					images.add(image);
				}
				rs.close();
				stmt.close();
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			return images;
		}
	
	 public static ArrayList<Image> getImagesByEntityGroupOrderByTime(
			int eType,int eid, String groupName, int start, int length) {
		ArrayList<Image> images = new ArrayList<Image>();
		
		StringBuffer sqlBuffer = new StringBuffer(String.format(
				"select id, time, group_id, path " +
				"from img " +
				"where etype = %d and eid = %d ", 
				eType, eid));
		if ( groupName != null ) {
			int groupID = Group.getGroupIdByName(groupName);
			sqlBuffer.append(String.format("and group_id = %d ", groupID));
		}
		sqlBuffer.append(String.format(
				"order by time desc " +
				"limit %d, %d",
				start, length));
		
		try {
			Connection conn = DBConnectionFactory.getConnection();
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(sqlBuffer.toString());
			while ( rs.next() ) {
				long time = rs.getLong("time");
				int id = rs.getInt("id");
				String mgroup = Group.getGroupNameById(rs.getInt("group_id"));
                String path = rs.getString("path");
				Image image = new Image(mgroup, time,path,eType,eid);
				image.setID(id);
				images.add(image);
			}
			rs.close();
			stmt.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return images;
	}
	 
	public static int getImagesTotalNumByEntityGroup(int eType,int eid, String groupName){
		int result = 0;
		
		StringBuffer sqlBuffer = new StringBuffer(String.format(
				"select count(*) as num " +
				"from img " +
				"where etype = %d and eid = %d ", 
				eType, eid));
		if ( groupName != null ) {
			int groupID = Group.getGroupIdByName(groupName);
			sqlBuffer.append(String.format("and group_id = %d ", groupID));
		}
		
		try {
			Connection conn = DBConnectionFactory.getConnection();
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(sqlBuffer.toString());
			if ( rs.next() ) {
				result = rs.getInt("num") ;
			}
			rs.close();
			stmt.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return result;
	}
	
	
	public String getPath() {
		return this.path;
	}
	
	public void serialize() {
		int groupID = Group.getGroupIdByName(groupName);
		if ( groupID < 0 ) return;
		try {
			Connection conn = DBConnectionFactory.getConnection();
			PreparedStatement stmt = conn.prepareStatement("insert into img(time, group_id, path, etype, eid) values(?,?,?,?,?)");
			stmt.setLong(1,new Long(this.time));
			stmt.setInt(2, groupID);
			stmt.setString(3, this.path);
			stmt.setInt(4, this.eType);
			stmt.setInt(5, this.eid);
			stmt.executeUpdate();
			stmt.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	public String getGroupName() {
		return groupName;
	}
	
	public long getTime() {
		return time;
	}
	
	public void setPath(String path) {
		this.path = path;
	}
	
	/**
	 * 删除与一个img有关的信息
	 * @param imgID
	 */
	public static void delete(int imgID){
		Connection connection = DBConnectionFactory.getConnection();
		
		try {
			Statement stmt = connection.createStatement();
			stmt.executeUpdate(String.format("delete from img where id=%d",imgID));
			stmt.executeUpdate(String.format("delete from img_prefer where img_id=%d",imgID));
			stmt.close();
			connection.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	@Override
	public int hashCode() {
		return new Long(time).hashCode() * 31 + groupName.hashCode();
	}
	
	@Override
	public boolean equals(Object obj) {
		return time == ((Image) obj).time && 
				groupName.equals(((Image) obj).groupName);
	}
}
